package com.zis.common.excel.io;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.zis.common.excel.db.DBRecord;
import com.zis.common.excel.db.DBRecordSet;
import com.zis.common.excel.metadata.ColumnMetadata;
import com.zis.common.excel.metadata.ReportMetadata;
import com.zis.common.excel.metadata.SheetMetadata;

/**
 * 
 * <b>说明：</b> excel导出：excel表格解析
 * 
 * @ClassName: ExcelReader
 * @author zhaohaitao(2543) 日期： 2015-7-24 上午11:21:20
 * 
 */
public class ExcelReader
{
    /**
     * 读取excel文件的路径
     */
    public static final String READPATH = "";
    
    public ExcelReader()
    {
    }
    
    public static DBRecordSet read(ReportMetadata reportMetadata)
    {
        DBRecordSet dbSet = null;
        try
        {
            if (READPATH == null || READPATH.trim().equals(""))
            {
                throw new Exception("文件路径不能为空。");
            }
            else
            {
                String reportName = reportMetadata.getReportName();
                HSSFWorkbook wb =
                    new HSSFWorkbook(new FileInputStream(
                        (new StringBuilder(String.valueOf(READPATH))).append(reportName).toString()));
                Map sheetMetadatas = reportMetadata.getSheetMetadatas();
                for (Iterator sheets = sheetMetadatas.keySet().iterator(); sheets.hasNext();)
                {
                    String sheetName = (String)sheets.next();
                    SheetMetadata sheetMetadata = (SheetMetadata)sheetMetadatas.get(sheetName);
                    HSSFSheet sheet = wb.getSheet(sheetName);
                    String direction = sheetMetadata.getDirection();
                    if (direction.equals("0"))
                    {
                        dbSet = readXSheet(sheet, sheetMetadata);
                    }
                    else if (direction.equals("1"))
                    {
                        dbSet = readYSheet(sheet, sheetMetadata);
                    }
                    else if (direction.equals("2"))
                    {
                        dbSet = readXYSheet(sheet, sheetMetadata);
                    }
                }
            }
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return dbSet;
    }
    
    private static DBRecordSet readXSheet(HSSFSheet sheet, SheetMetadata sheetMetadata)
    {
        DBRecordSet dbSet = null;
        try
        {
            Map columnMetadatas = sheetMetadata.getColumnMetadatas();
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++)
            {
                HSSFRow row = sheet.getRow(rowIndex);
                if (!isBlankRow(row))
                {
                    DBRecord record = new DBRecord();
                    for (Iterator columns = columnMetadatas.keySet().iterator(); columns.hasNext();)
                    {
                        String columnName = (String)columns.next();
                        ColumnMetadata columnMetadata = (ColumnMetadata)columnMetadatas.get(columnName);
                        String dbName = columnMetadata.getDbColumn();
                        String excelNum = columnMetadata.getExcelColumn();
                        HSSFCell cell = row.getCell(Integer.parseInt(excelNum));
                        if (cell != null)
                        {
                            String value = getStringCellValue(cell);
                            record.put(dbName, value);
                        }
                    }
                    
                    if (dbSet == null)
                    {
                        dbSet = new DBRecordSet();
                    }
                    dbSet.add(record);
                }
            }
            
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return dbSet;
    }
    
    private static DBRecordSet readYSheet(HSSFSheet sheet, SheetMetadata sheetMetadata)
    {
        DBRecordSet dbSet = null;
        try
        {
            Map columnMetadatas = sheetMetadata.getColumnMetadatas();
            for (int columnIndex = 1; columnIndex <= sheet.getRow(0).getLastCellNum(); columnIndex++)
            {
                DBRecord record = new DBRecord();
                for (Iterator columns = columnMetadatas.keySet().iterator(); columns.hasNext();)
                {
                    String columnName = (String)columns.next();
                    ColumnMetadata columnMetadata = (ColumnMetadata)columnMetadatas.get(columnName);
                    String dbName = columnMetadata.getDbColumn();
                    String excelNum = columnMetadata.getExcelColumn();
                    HSSFRow row = sheet.getRow(Integer.parseInt(excelNum));
                    if (!isBlankRow(row))
                    {
                        HSSFCell cell = row.getCell(columnIndex);
                        if (cell != null)
                        {
                            String value = getStringCellValue(cell);
                            record.put(dbName, value);
                        }
                    }
                }
                if (dbSet == null)
                {
                    dbSet = new DBRecordSet();
                }
                dbSet.add(record);
            }
            
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return dbSet;
    }
    
    private static DBRecordSet readXYSheet(HSSFSheet sheet, SheetMetadata sheetMetadata)
    {
        DBRecordSet dbSet = null;
        return dbSet;
    }
    
    private static String getStringCellValue(HSSFCell cell)
    {
        String strCell = null;
        if (cell != null)
            switch (cell.getCellType())
            {
                case 2: // '\002'
                case 3: // '\003'
                default:
                    break;
                case 1: // '\001'
                    strCell = cell.getStringCellValue().trim();
                    break;
                case 0: // '\0'
                    if (HSSFDateUtil.isCellDateFormatted(cell))
                    {
                        java.util.Date date = cell.getDateCellValue();
                        strCell = (new SimpleDateFormat("yyyy-MM-dd")).format(date).trim();
                        break;
                    }
                    double num = cell.getNumericCellValue();
                    if (num - (double)(int)num < 4.9406564584124654E-324D)
                    {
                        strCell = Integer.toString((int)num).trim();
                        break;
                    }
                    String numStr = Double.toString(num);
                    String regex = "^((\\d+.?\\d+)[Ee]{1}(\\d+))$";
                    Pattern pattern = Pattern.compile(regex);
                    Matcher matcher = pattern.matcher(numStr);
                    if (matcher.find())
                    {
                        BigDecimal decimal = new BigDecimal(num);
                        strCell = decimal.toPlainString().trim();
                    }
                    else
                    {
                        strCell = numStr.trim();
                    }
                    break;
                
                case 4: // '\004'
                    strCell = String.valueOf(cell.getBooleanCellValue()).trim();
                    break;
            }
        return strCell;
    }
    
    public static boolean isBlankRow(HSSFRow row)
    {
        if (row == null)
            return true;
        boolean result = true;
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
        {
            HSSFCell cell = row.getCell(i, HSSFRow.RETURN_BLANK_AS_NULL);
            if (cell == null)
            {
                continue;
            }
            String value = getStringCellValue(cell);
            if (value == null || value.trim().isEmpty())
            {
                continue;
            }
            result = false;
            break;
        }
        return result;
    }
    
}
